package com.hassean.we.admin.controller;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.hassean.we.admin.common.constant.CommonConstant;
import com.hassean.we.admin.common.util.PrepareCall;
import com.hassean.we.admin.common.util.Query;
import com.hassean.we.admin.common.util.R;
import com.hassean.we.admin.common.web.BaseController;
import com.hassean.we.admin.model.entity.TbTerminalTrajectory;
import com.hassean.we.admin.model.entity.TbTerminalTrajectory201901;
import com.hassean.we.admin.service.TbTerminalLocationService;
import com.hassean.we.admin.service.TbTerminalTrajectory201901Service;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * <p>
 *  前端控制器
 *  备注：兼容保存N年的历史轨迹
 * </p>
 *
 * @author yajun
 * @since 2019-03-29
 */
@Api(description = "终端轨迹接口")
@RestController
@RequestMapping("/tbTerminalTrajectory")
public class TbTerminalTrajectoryController extends BaseController {
    @Autowired
    private TbTerminalTrajectory201901Service tbTerminalTrajectory201901Service;
    @Autowired
    private TbTerminalLocationService tbTerminalLocationService;
    @Autowired
    SqlSessionFactory sqlSessionFactory;


    /**
     * 通过ID查询
     *
     * @param id ID
     * @return TbTerminalTrajectory201901
     */
    @GetMapping("/{id}")
    public R<TbTerminalTrajectory201901> get(@PathVariable Long id) {
        return new R<>(tbTerminalTrajectory201901Service.selectById(id));
    }


    /**
     * 分页查询信息
     *
     * @param params 分页对象
     * @return 分页对象
     */
    @RequestMapping("/page")
    public Page page(@RequestParam Map<String, Object> params) {
        params.put(CommonConstant.DEL_FLAG, CommonConstant.STATUS_NORMAL);
        return tbTerminalTrajectory201901Service.selectPage(new Query<>(params), new EntityWrapper<>());

    }

    /**
     * 添加
     *
     * @param tbTerminalTrajectory201901 实体
     * @return success/false
     */
    @PostMapping
    public R<Boolean> add(@RequestBody TbTerminalTrajectory201901 tbTerminalTrajectory201901) {
        return new R<>(tbTerminalTrajectory201901Service.insert(tbTerminalTrajectory201901));
    }

    /**
     * 删除
     *
     * @param id ID
     * @return success/false
     */
    @DeleteMapping("/{id}")
    public R<Boolean> delete(@PathVariable Long id) {
        TbTerminalTrajectory201901 tbTerminalTrajectory201901 = new TbTerminalTrajectory201901();
        tbTerminalTrajectory201901.setTerminalTrajectoryId(id);
        tbTerminalTrajectory201901.setUpdateTime(new Date());
        tbTerminalTrajectory201901.setDelFlag(CommonConstant.STATUS_DEL);
        return new R<>(tbTerminalTrajectory201901Service.updateById(tbTerminalTrajectory201901));
    }

    /**
     * 编辑
     *
     * @param tbTerminalTrajectory201901 实体
     * @return success/false
     */
    @PutMapping
    public R<Boolean> edit(@RequestBody TbTerminalTrajectory201901 tbTerminalTrajectory201901) {
        tbTerminalTrajectory201901.setUpdateTime(new Date());
        return new R<>(tbTerminalTrajectory201901Service.updateById(tbTerminalTrajectory201901));
    }


    /**
     * 通过存储过程查询数据
     *获取某终端的轨迹
     * @return  R<List>
     */
    @ApiOperation(value = "通过存储过程查询数据" ,  notes="获取某终端的轨迹")
    @RequestMapping("/getTrajectory")
    public R<List> getTrajectory(@RequestParam Map<String, Object> params) {
        Integer terminal_id  =null;//终端id
        String startTime = null;//开始时间
        String endTime = null;//结束时间
        if (params.get("terminal_id")!=null&&!(params.get("terminal_id")).equals("")){
             terminal_id = Integer.parseInt(params.get("terminal_id").toString());
            System.out.println("terminal_id:"+terminal_id);
        }
        if (params.get("startTime")!=null&&!(params.get("startTime")).equals("")){
            startTime =  params.get("startTime").toString();
            startTime = "'"+startTime+"'";
            System.out.println("startTime:"+startTime);
        }else{
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            startTime = sdf.format(new Date()); //默认今天
            startTime = "'"+startTime+"'";
            System.out.println("startTime:"+startTime);
        }
        if (params.get("endTime")!=null&&!(params.get("endTime")).equals("")){
            endTime =  params.get("endTime").toString();
            endTime = "'"+endTime+"'";
            System.out.println("endTime:"+endTime);
        }else{
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            endTime = sdf.format(new Date()); //默认今天
            endTime = "'"+endTime+"'";
        }

        List<Object> list = new ArrayList<Object>(); //实例化List对象
        PrepareCall prepareCall = new PrepareCall();
        Connection conn = prepareCall.getConnection();  //创建数据库连接
        try {
            //调用存储过程
            CallableStatement cs = conn.prepareCall("{call sp_getTrajectory(?,?,?,?)}");
            /**
             * 数据库函数代码如下
             *  drop PROCEDURE  sp_getTrajectory;
             DELIMITER//
             CREATE PROCEDURE sp_getTrajectory(in terminal_id int,IN yearMouth varchar(30),IN startTime varchar(30),IN endTime varchar(30))
             BEGIN

             SET @sp_getTrajectoryStr:=CONCAT("select * from tb_terminal_trajectory_",yearMouth," where terminal_id =",terminal_id," and date_format(add_time,'%Y-%m-%d %H:%i')>=",startTime," and date_format(add_time,'%Y-%m-%d %H:%i')<=",endTime);
             PREPARE stmt from @sp_getTrajectoryStr;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;
             END
             //

             set @startTime="'2019-04-22 14:13:06'";
             set @endTime="'2019-04-22 14:26:36'";
             set @terminal_id =1;
             set @yearMouth = '201904';
             call sp_getTrajectory(@terminal_id,@yearMouth,@startTime,@endTime);

             */


            //截取年月
            String yearMouth = startTime.substring(1,8).replace("-","");//yearMouth:201902
            System.out.println("yearMouth:"+yearMouth.replace("-",""));


            cs.setInt(1, terminal_id);
            cs.setString(2,yearMouth);
            cs.setString(3,startTime);
            cs.setString(4,endTime);
            ResultSet rs = cs.executeQuery(); //执行查询操作，并获取结果集
            //for循环获取值
            while(rs.next())
            {
                //轨迹通用的model
                TbTerminalTrajectory tbTerminalTrajectory = new TbTerminalTrajectory();
                Long trajectoryId = rs.getLong("terminal_trajectory_id");
                tbTerminalTrajectory.setTerminalTrajectoryId(trajectoryId);
                Long terminalId = rs.getLong("terminal_id");
                tbTerminalTrajectory.setTerminalId(terminalId);
                Double longitude = rs.getDouble("longitude");
                tbTerminalTrajectory.setLongitude(longitude);
                Double latitude = rs.getDouble("latitude");
                tbTerminalTrajectory.setLatitude(latitude);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String addTime = sdf.format(rs.getTimestamp("add_time"));
                tbTerminalTrajectory.setAddTime(addTime);
                list.add(tbTerminalTrajectory);
            }
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  new R<>(list); //返回list

    }
    /**
     * 添加轨迹数据
     * 自动插入对应的表数据
     */
    @ApiOperation(value = "添加轨迹数据" ,  notes="自动插入对应的表数据")
    @RequestMapping("/addTerminalTrajectory")
    public R<Boolean> addTerminalTrajectory(){
        Integer terminal_id =1;
        Double longitude =112.345;
        Double latitude = 23.44;
        String add_time ="'2019-04-24 15:55:00'";


        PrepareCall prepareCall = new PrepareCall();
        Connection conn = prepareCall.getConnection();  //创建数据库连接
        try {
            //调用存储过程
            CallableStatement cs = conn.prepareCall("{call sp_insert_trajectory(?,?,?,?)}");
            //mysql数据库代码
            /**
             * 动态插入
             *drop PROCEDURE  sp_insert_trajectory;
             DELIMITER//
             CREATE PROCEDURE sp_insert_trajectory(in terminal_id int(20),in longitude DOUBLE(10,6),in latitude DOUBLE(10,6),in add_time VARCHAR(30))
             BEGIN
             SET @dateStr = DATE_FORMAT(NOW(), '%Y%m');
             SET @sqlStr:=CONCAT("insert into tb_terminal_trajectory_",@dateStr," (terminal_id,longitude,latitude,add_time)
             VALUES(",terminal_id,",",longitude,",",latitude,",",add_time,")");
             PREPARE stmt from @sqlStr;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;
             END
             //

             SET @longitude =112.345;
             SET @latitude = 23.44;
             set @add_time ="'2019-04-24 15:55:00'";
             set @terminal_id =1;
             call sp_insert_trajectory(@terminal_id,@longitude,@latitude,@add_time);
             */
            cs.setInt(1, terminal_id);
            cs.setDouble(2,longitude);
            cs.setDouble(3,latitude);
            cs.setString(4,add_time);
            ResultSet rs = cs.executeQuery(); //执行操作，并获取结果集


            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  new R<>();
    }
}